package com.api.gbhg.huangwm.dao;

import cn.hutool.core.util.StrUtil;
import com.customization.assets.util.DateTools;
import com.customization.commons.LogTool;
import org.apache.commons.lang.StringUtils;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;
import weaver.hrm.User;

import java.sql.SQLException;
import java.util.*;

/**
 * @Author Huangwm
 * @Date 2022-06-22
 * @Version 1.0
 * @Description TODO 预算调整功能逻辑
 **/
public class YuSuanTiaoZhengDao {
    private LogTool logTool = new LogTool("/log/gbhg/huangwm/dao/YuSuanTiaoZhengDao",false);
    private DateTools dateTools = new DateTools();
    /**
     * 判断当前用户所在的独立核算单位是否存在机动经费
     * @param user
     * @return
     */
    public Map<String,Object> hasMobileFunds(User user){
        Map<String,Object> apiResult = new HashMap<>();
        apiResult.put("apiStatus",true);
        try{
            RecordSet rs = new RecordSet();
            String sql = "select lcjlsqjlid,xmmin from uf_jingfei_version where ysnd="+dateTools.getCurrentYear()+" and dlhsdw=(\n" +
                    "select case when exists (select dlhsdw from uf_dlhsdwgl where dlhsdw="+user.getUserSubCompany1()+") then (select dlhsdw from uf_dlhsdwgl where dlhsdw=\"+user.getUserSubCompany1()+\") else (select supsubcomid from hrmsubcompany where id="+user.getUserSubCompany1()+") end \n" +
                    ") and lcbj=19";
            logTool.writeLog("查询机动经费SQL："+sql);
            if(rs.execute(sql)){
                if(rs.next()){
                    apiResult.put("hasRight",true);
                    apiResult.put("xmid",rs.getString("lcjlsqjlid"));
                    apiResult.put("xmid",rs.getString("xmmin"));
                }else{
                    apiResult.put("hasRight",false);
                    apiResult.put("msg","未查询到机动经费");
                }
            }else{
                apiResult.put("hasRight",false);
                apiResult.put("msg","执行查询机动经费SQL异常("+sql+")");
            }
        }catch (Exception e){
            apiResult.put("apiStatus",false);
            apiResult.put("hasRight",false);
            apiResult.put("msg","执行查询机动经费接口异常("+e.getMessage()+")");
        }
        return apiResult;
    }

    /**
     * 根据id获取中央下级收入的所有上级id 包含本身
     * @param id
     * @return
     */
    public List<String> getSuperiorId(String id){
        List<String> list = new ArrayList<>();
        RecordSet rs = new RecordSet();
        String sql = " select sjzxsr from  uf_zyczbksr_xj where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        if (StrUtil.isNotEmpty(rs.getString("sjzxsr"))){
            List<String> sjzxsr = getSuperiorId(rs.getInt("sjzxsr")+"");
            list.addAll(sjzxsr);
        }
        list.add(String.valueOf(id));
        return list;
    }

    /**
     * 获取中央财政拨款收入层级名称
     * @param zcczbksr
     * @return
     */
    public String getZyczbksrName(String zcczbksr){
        logTool.writeLog("获取中央财政拨款收入层级名称："+ zcczbksr);
        if("".equals(zcczbksr) || zcczbksr == ""){
            return "";
        }
        String name = "";
        RecordSet rs = new RecordSet();
        String kmj = new BaseBean().getPropValue("Verification","yj");
        if(zcczbksr.split("_")[0].equals(kmj)){
            rs.execute("select kmdm from uf_zyczbksr_km where id="+zcczbksr.split("_")[1]);
            rs.next();
            name =  rs.getString("kmdm");
        }else{
            rs.execute("select kmdm from uf_zyczbksr_km where id=(select szgnkm from uf_zyczbksr_xj where id="+zcczbksr.split("_")[1]+")");
            rs.next();
            name+=rs.getString("kmdm");
            RecordSet rs2 = new RecordSet();
            List<String> superiorId = getSuperiorId(zcczbksr.split("_")[1]);
            rs2.execute("select srzx from uf_zyczbksr_xj where id in("+StringUtils.join(superiorId.toArray(),",")+") ");
            while (rs2.next()){
                name+="/"+rs2.getString("srzx");
            }
        }
        return name;
    }

    /**
     * 支出渠道调整时获取项目信息
     * @param xmid
     * @return
     */
    public Map<String,Object> getProjectInfo(String xmid){
        Map<String,Object> apiResult = new HashMap<>();
        apiResult.put("apiStatus",true);
        try{
            RecordSet rs = new RecordSet();
            Double ysze = 0.00;
            String sql1 = "select xmmin,jbr,sbdw,dlhsdw,ysnd,t2.lastname as jbrname,t3.subcompanyname as sbdwname,\n" +
                    "t4.subcompanyname as dlhsdwname  from uf_jingfei_version as t1\n" +
                    "left join hrmresource as t2 on t2.id=t1.jbr\n" +
                    "left join hrmsubcompany as t3 on t3.id=t1.sbdw\n" +
                    "left join hrmsubcompany as t4 on t4.id=t1.dlhsdw\n" +
                    "where lcjlsqjlid="+xmid;
            Map<String,Object> mainData = new HashMap<>();
            mainData.put("xmid",xmid);
            String sql2 = "select t1.bmzcjjfl,t1.zfjjfl,t1.ze,t1.dj,t1.ybx,t1.syed,t2.id as bmjjflid,t3.id as zfjjflid from uf_jingfei as t1\n" +
                    "left join uf_jjzcflkmk as t2 on t1.bmzcjjfl=t2.kdm\n" +
                    "left join uf_jjzcflkmk as t3 on t1.zfjjfl=t3.kdm\n" +
                    "where lcjlid="+xmid;
            List<Map<String,Object>> jjflList = new ArrayList<>();
//            String sql3 = "select yslylx,je,syed,srlx,zcqd,dfczbkxm,zyczbksr,\n" +
//                    "(select mc from uf_srysfl where id=zcqd)as zcqdmc,\n"+
//                    "(select xmmc from uf_fzysrxm where id=dfczbkxm)as dfczbkxmmc \n" +
//                    "from uf_jingfeilaiyuan where lcid="+xmid;
            String sql3 = "SELECT  t.*,\n" +
                    "(SELECT id FROM dbo.uf_gxmdygnkm WHERE xmlcid="+xmid+" AND hpssjid=t.zyczbksr AND zfqd=t.ysly) AS xzkmdm,\n" +
                    "(SELECT dygnkmmc FROM dbo.uf_gxmdygnkm WHERE xmlcid="+xmid+" AND hpssjid=t.zyczbksr AND zfqd=t.ysly)AS dygnkmmc FROM (\n" +
                    "select id,yslylx,je,syed,srlx,zcqd,dfczbkxm,zyczbksr,\n" +
                    "(select mc from uf_srysfl where id=zcqd)as zcqdmc,\n" +
                    "(select xmmc from uf_fzysrxm where id=dfczbkxm)as dfczbkxmmc,\n" +
                    "(CASE yslylx WHEN '上年结转核批' then 1 ELSE 0 end) AS ysly\n" +
                    "from uf_jingfeilaiyuan where lcid="+xmid+" ) t";
            List<Map<String,Object>> hplyList = new ArrayList<>();
            if(rs.execute(sql1) && rs.next()){
                mainData.put("xmmin",rs.getString("xmmin"));
                mainData.put("ysnd",rs.getString("ysnd"));
                mainData.put("jbrname",rs.getString("jbrname"));
                mainData.put("sbdwname",rs.getString("sbdwname"));
                mainData.put("dlhsdwname",rs.getString("dlhsdwname"));
                mainData.put("jbrid",rs.getString("jbr"));
                mainData.put("sbdwid",rs.getString("sbdw"));
                mainData.put("dlhsdwid",rs.getString("dlhsdw"));
            }
            if(rs.execute(sql2)){
                while (rs.next()){
                    Map<String,Object> jjfl = new HashMap<>();
                    jjfl.put("bmzcjjfl",rs.getString("bmzcjjfl"));
                    jjfl.put("zfjjfl",rs.getString("zfjjfl"));
                    jjfl.put("ze",rs.getString("ze"));
                    jjfl.put("dj",rs.getString("dj"));
                    jjfl.put("ybx",rs.getString("ybx"));
                    jjfl.put("syed",rs.getString("syed"));
                    jjfl.put("bmjjflid",rs.getString("bmjjflid"));
                    jjfl.put("zfjjflid",rs.getString("zfjjflid"));
                    jjflList.add(jjfl);
                    ysze += rs.getDouble("ze");
                }
            }

            if(rs.execute(sql3)){
                while (rs.next()){
                    Map<String,Object> hply = new HashMap<>();
                    hply.put("yslylx",rs.getString("yslylx"));
                    hply.put("je",rs.getString("je"));
                    hply.put("syed",rs.getString("syed"));
                    hply.put("srlx",rs.getString("srlx").equals("0")?"财政拨款":"非财政拨款");
                    hply.put("zcqd",rs.getString("zcqd"));
                    hply.put("fzysrfl",rs.getString("zcqdmc"));
                    hply.put("dfczbkxm",rs.getString("dfczbkxm"));
                    hply.put("zyczbksr",rs.getString("zyczbksr"));
                    hply.put("dfczbkxmmc",rs.getString("dfczbkxmmc"));
                    hply.put("yslymc",rs.getString("srlx").equals("0")
                            ?getZyczbksrName(rs.getString("zyczbksr"))
                            :rs.getString("zcqdmc")+"/"+rs.getString("dfczbkxmmc"));
                    hply.put("xzkmdm",rs.getString("xzkmdm"));
                    hply.put("dygnkmmc",rs.getString("dygnkmmc"));
                    hply.put("id",rs.getString("id"));
                    hply.put("ysly",rs.getString("ysly"));
                    hply.put("srlxid",rs.getString("srlx"));
                    hplyList.add(hply);
                }
            }
            mainData.put("ysze",ysze);
            apiResult.put("main",mainData);
            apiResult.put("jjfl",jjflList);
            apiResult.put("hply",hplyList);
        }catch (Exception e){
            apiResult.put("apiStatus",false);
            apiResult.put("msg","支出渠道调整获取项目信息异常("+e.getMessage()+")");
        }
        return apiResult;
    }

    /**
     *经费额度信息插入
     * @param param
     * @return
     */
    public boolean insertBudgeLines(Map<String,String> param){
        RecordSetTrans rts=new RecordSetTrans();
        String sql="insert into uf_jingfei(requestId,lcbj,lcjlid,bmzcjjfl,zfjjfl,nd,ze,syed,dj,sbdw,xmmc,modedatamodifier,modedatamodifydatetime,gkdw,sydw)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            rts.executeUpdate(sql,
                    param.getOrDefault("requestId",""),
                    param.getOrDefault("lcbj",""),
                    param.getOrDefault("lcjlid",""),
                    param.getOrDefault("bmzcjjfl",""),
                    param.getOrDefault("zfjjfl",""),
                    param.getOrDefault("nd",""),
                    param.getOrDefault("ze",""),
                    param.getOrDefault("syed",""),
                    param.getOrDefault("dj",""),
                    param.getOrDefault("sbdw",""),
                    param.getOrDefault("xmmc",""),
                    param.getOrDefault("modedatamodifier",""),
                    param.getOrDefault("modedatamodifydatetime",""),
                    param.getOrDefault("gkdw",""),
                    param.getOrDefault("sydw","")
            );
            logTool.writeLog("经费额度信息插入"+sql);
        } catch (Exception e) {
            e.printStackTrace();
            rts.rollback();
            return false;
        }
        rts.commit();
        return true;
    }
    /**
     * 根据流程主表id获取流程倒数第2，3明细表名称（核批渠道）非总署项目
     * @param formid
     * @return
     */
    public List<String> getHpqd_DtTablesNoZSXM(int formid) {
        List<String> resList = new ArrayList<>();
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + formid + " order by tablename desc;");
        rs.next();
        int i = 0;
        while (i < 2) {
            rs.next();
            resList.add(rs.getString("tablename"));
            i++;
        }
        return resList;
    }

    /**
     * 根据流程主表id获取流程倒数第4明细表名称（细化经济分类）非总署项目
     * @param formid
     * @return
     */
    public String getJjfl_DtTablesNoZSXM(int formid) {
        String resList = "";
        RecordSet rs = new RecordSet();
        rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + formid + " order by tablename desc;");
        int i = 1;
        int dt = 4;
        while (rs.next()) {
            if(i==dt){
                resList = rs.getString("tablename");
            }
            i++;
        }
        return resList;
    }

    /**
     * 删除jingfei表数据
     * @param requestid
     */
    public void deleteJingFeiByRequestid(String requestid){
        RecordSet rs=new RecordSet();
        try {
            rs.executeUpdate("delete from uf_jingfei where lcjlid =" + requestid);
            rs.executeUpdate("delete from uf_jingfeiVersion where lcjlid =" + requestid);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
    public  RecordSet theCentral(String zcqd,int year,String sbdw){
        RecordSet rs = new RecordSet();
        String sql = "select gnkm from uf_fzysr where szfl="+zcqd+" and nf="+year+" and dw="+sbdw+"";
        logTool.writeLog("sql:"+sql);
        rs.executeQuery(sql);
        return rs;
    }

    /**
     * 根据指定分隔符拼接字符串
     * @param strings 字符串集合
     * @param split 分隔符
     * @return
     */
    public String joinStringBySplit(List<String> strings, String split) {
        return strings.stream().reduce((x, y) -> x + split + y).orElse(null);
    }

    /**
     * 生成指定数量的占位符
     * @param cnt 数目
     * @return
     */
    public String getQuestionMark(int cnt) {
        StringBuilder stringBuffer = new StringBuilder();
        for (int i = 0; i < cnt; i++) {
            if (i == 0) {
                stringBuffer.append("?");
            } else {
                stringBuffer.append(",?");
            }
        }
        return stringBuffer.toString();
    }
    /**
     * 根据表名，表字段生成sql插入语句
     * @param tableFields
     * @param tableName
     * @return
     */
    public  String getInsertSql(List<String> tableFields, String tableName) {
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("insert into " + tableName + " (");
        stringBuffer.append(joinStringBySplit(tableFields, ","));
        stringBuffer.append(") values (");
        stringBuffer.append(getQuestionMark(tableFields.size()));
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    /* *
     * 检测Map空的key删除
     * @Param [param]
     * @return
     **/
    public Map<String, Object> removeNullString(Map<String, Object> param) {
        try {
            Iterator<String> it = param.keySet().iterator();
            while (it.hasNext()) {
                Object ele = it.next();
                if (param.get(ele).toString().trim().equals("")) {
                    //hashMap.remove(ele);    //出错 修改了映射结构 影响了迭代器遍历
                    it.remove();              //用迭代器删除 则不会出错
                }
            }
            return param;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean storageDateToJingfeiLy(Map[] maps) {
        boolean flag = false;
        for (int i = 0; i < maps.length; i++) {
            Map map = removeNullString(maps[i]);
            Set set = map.keySet();
            List<String> listField = new ArrayList<String>(set);
            // 上年结转
            Collection<String> valueCollection = map.values();
            List<String> valueList = new ArrayList<>(valueCollection);
            // 存储数据到经费来源表
            RecordSetTrans rst = new RecordSetTrans();
            String uf_jingfeilaiyuansql = getInsertSql(listField, "uf_jingfeilaiyuan");
            try {
                rst.executeUpdate(uf_jingfeilaiyuansql, valueList.toArray());
            } catch (Exception e) {
                e.printStackTrace();
                logTool.writeLog("同步经费来源sql---->" + uf_jingfeilaiyuansql + valueCollection);
                logTool.writeLog("errorsql==" + e.toString());
                return  false;
            }
            flag = true;
        }
        return flag;
    }

    /**
     * 根据id查功能科目
     * @param id
     * @return
     */
    public String getSubjectsGnkm(int id){
        RecordSet rs = new RecordSet();
        String sql = "select gnkm from uf_zyczbksr_km where id = "+id+"";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("gnkm");
    }
    public int getSubjectsGnkm2(int id){
        RecordSet rs = new RecordSet();
        String sql = "select szgnkm from uf_zyczbksr_xj where id = "+id;
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("szgnkm");
    }

    /**
     * 插入uf_jingfei_version表
     * @param table
     * @param requestid
     * @return
     * @throws Exception
     */
    public boolean jingFeiVersionTableValues(String table,String requestid) throws Exception {
        RecordSetTrans rst = new RecordSetTrans();
        rst.executeSql("delete from uf_jingfei_version where requestid="+requestid);
        String versionFields = "requestid,lcid,lcbj,lcjlsqjlid,lczt,ysnd,jbr,xmmin,fzr,yszje,sbdw,sbks,lxdh,lcztz,sfky,dlhsdw,sydw";
        String sql = "select fm.requestid,wr.workflowid,flowtype,fm.requestid as lcjlsqjlid,'归档' as lczt,ysnd,jbr,xmmc,fzr,sqzey,sbdw,sbks,lxdh,3 as lcztz,1 as sfky,szdlhsdw,sydw\n" +
                "from "+table+" fm\n" +
                "left join workflow_requestbase wr on wr.requestid=fm.requestid\n"+
                "where fm.requestid="+requestid;
        rst.execute(sql);
        if(rst.next()){
            Vector vector = rst.getArray();
            Object[] values = (Object[]) Arrays.stream(vector.toArray()).findFirst().orElse(null);
            rst.setAutoCommit(false);
            String insertSql = "insert into uf_jingfei_version("+versionFields+")values("+getQuestionMark(versionFields.split(",").length)+")";
            if(rst.executeUpdate(insertSql,values)){
                rst.commit();
                return  true;
            }else{
                rst.rollback();
                return false;
            }
        }
        return false;
    }
}
